﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;

/// <summary>
/// Summary description for DatabaseAccess
/// </summary>
public class DatabaseAccess
{
    String connectionString = "Data Source=OLSON140SERVER;Initial Catalog=BPI_Library;Integrated Security=True";

    public DatabaseAccess()
	{
		//
		// TODO: Add constructor logic here
		//
	}
    //search by barcode, title, author, or callNumber
    //for Librarian Manage Book - Jacquie
    public SqlDataAdapter LibrarianSearchBook(String searchTxt)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlDataAdapter sqlCommand = null;
        
        con.Open();
        sqlCommand = new SqlDataAdapter("exec usp_BOOK_LibrarianBookSearch '" + searchTxt + "'", con);
        con.Close();

        return sqlCommand;
    }


    public SqlDataAdapter SearchForBook(int ColumnID, String Criteria)
    {
        SqlConnection con = new SqlConnection(connectionString);

        SqlDataAdapter sqlCommand = null;

        con.Open();

        switch (ColumnID)
        {
            case Book.BARCODE_CODE:
                sqlCommand = new SqlDataAdapter("exec BOOK_SELECT_LIKE_BARCODE '" + Criteria + "'", con);
                break;
            case Book.AUTHOR_CODE:
                sqlCommand = new SqlDataAdapter("exec BOOK_SELECT_LIKE_AUTHOR '" + Criteria + "'", con);
                break;
            case Book.SUBJECT_CODE:
                sqlCommand = new SqlDataAdapter("exec BOOK_SELECT_LIKE_SUBJECT '" + Criteria + "'", con);
                break;
            case Book.TITLE_CODE:
                sqlCommand = new SqlDataAdapter("exec BOOK_SELECT_LIKE_TITLE '" + Criteria + "'", con);
                break;
            case Book.KEYWORDS_CODE:
                sqlCommand = new SqlDataAdapter("exec dbo.BOOK_SELECT_LIKE_KEYWORD '" + Criteria + "'", con);
                break;
            case Book.ISBN_CODE:
                sqlCommand = new SqlDataAdapter("exec BOOK_SELECT_LIKE_ISBN '" + Criteria + "'", con);
                break;
        }
        con.Close();
        
        return sqlCommand;
    }
    //add Person to database
    public void addPerson(string firstName, string lastName, string address, string city, string State,
                          int zip, string phoneNumber, string email, Boolean wantNewsLetter, int LibraryId)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlCommand sqlCom = new SqlCommand("PERSON_INSERT", sqlCon);
        sqlCom.CommandType = CommandType.StoredProcedure;

        sqlCom.Parameters.Add("@FirstName", firstName);
        sqlCom.Parameters.Add("@LastName", lastName);
        sqlCom.Parameters.Add("@Address", address);
        sqlCom.Parameters.Add("@City", city);
        sqlCom.Parameters.Add("@State", State);
        sqlCom.Parameters.Add("@Zip", zip);
        sqlCom.Parameters.Add("@PhoneNumber", phoneNumber);
        sqlCom.Parameters.Add("@Email", email);
        sqlCom.Parameters.Add("@wantsNewsletter", wantNewsLetter);
        sqlCom.Parameters.Add("@LibraryId", LibraryId);

        sqlCon.Open();
        sqlCom.ExecuteNonQuery();
        sqlCon.Close();
    }
    //add patron to database
    public void addPatron(string firstName, string lastName, string address, string city, string State, 
                          int zip, string phoneNumber, string email, Boolean wantNewsLetter, int PersonId, 
                          Boolean isAdult, Boolean wantsNotifications, string PreferredContactMethod,
                          DateTime CardExpires, string Password)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlCommand sqlCom = new SqlCommand("usp_PERSON_InsertPersonPatron", sqlCon);
        sqlCom.CommandType = CommandType.StoredProcedure;

        sqlCom.Parameters.Add("@FirstName", firstName);
	    sqlCom.Parameters.Add("@LastName", lastName);
	    sqlCom.Parameters.Add("@Address", address);
	    sqlCom.Parameters.Add("@City", city);
	    sqlCom.Parameters.Add("@State", State);
	    sqlCom.Parameters.Add("@Zip", zip);
	    sqlCom.Parameters.Add("@PhoneNumber", phoneNumber);
	    sqlCom.Parameters.Add("@Email", email);
	    sqlCom.Parameters.Add("@wantsNewsletter", wantNewsLetter);
	    sqlCom.Parameters.Add("@PersonId", PersonId);
	    sqlCom.Parameters.Add("@isAdult", isAdult);
	    sqlCom.Parameters.Add("@wantsNotifications", wantsNotifications);
	    sqlCom.Parameters.Add("@PreferredContactMethod", PreferredContactMethod);
	    sqlCom.Parameters.Add("@CardExpires", CardExpires);
	    sqlCom.Parameters.Add("@Password", Password);

        sqlCon.Open();
        sqlCom.ExecuteNonQuery();
        sqlCon.Close();
    }

    //Update patron
    public void UpdatePatron(string firstName, string lastName, string address, string city, string State,
                             int zip, string phoneNumber, string email, Boolean wantNewsLetter, int PersonId,
                             Boolean isAdult, Boolean wantsNotifications, string PreferredContactMethod,
                             DateTime CardExpires, string Password)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlCommand sqlCom = new SqlCommand("usp_PERSON_UpdatePersonPatron", sqlCon);
        sqlCom.CommandType = CommandType.StoredProcedure;

        sqlCom.Parameters.Add("@FirstName", firstName);
        sqlCom.Parameters.Add("@LastName", lastName);
        sqlCom.Parameters.Add("@Address", address);
        sqlCom.Parameters.Add("@City", city);
        sqlCom.Parameters.Add("@State", State);
        sqlCom.Parameters.Add("@Zip", zip);
        sqlCom.Parameters.Add("@PhoneNumber", phoneNumber);
        sqlCom.Parameters.Add("@Email", email);
        sqlCom.Parameters.Add("@wantsNewsletter", wantNewsLetter);
        sqlCom.Parameters.Add("@PersonId", PersonId);
        sqlCom.Parameters.Add("@isAdult", isAdult);
        sqlCom.Parameters.Add("@wantsNotifications", wantsNotifications);
        sqlCom.Parameters.Add("@PreferredContactMethod", PreferredContactMethod);
        sqlCom.Parameters.Add("@CardExpires", CardExpires);
        sqlCom.Parameters.Add("@Password", Password);

        sqlCon.Open();
        sqlCom.ExecuteNonQuery();
        sqlCon.Close();
    }
    //Update person
    public void UpdatePerson(Person per)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlCommand sqlCom = new SqlCommand("PERSON_UPDATE", sqlCon);
        sqlCom.CommandType = CommandType.StoredProcedure;

        sqlCom.Parameters.Add("@PersonId", per.PersonID);
        sqlCom.Parameters.Add("@FirstName", per.FirstName);
        sqlCom.Parameters.Add("@LastName", per.LastName);
        sqlCom.Parameters.Add("@Address", per.Address);
        sqlCom.Parameters.Add("@City", per.City);
        sqlCom.Parameters.Add("@State", per.State);
        sqlCom.Parameters.Add("@Zip", per.Zip);
        sqlCom.Parameters.Add("@PhoneNumber", per.PhoneNumber);
        sqlCom.Parameters.Add("@Email", per.Email);
        sqlCom.Parameters.Add("@wantsNewsletter", per.wantsNewsletter);
        
        sqlCon.Open();
        sqlCom.ExecuteNonQuery();
        sqlCon.Close();
    }

    //Get Patron
    public Patron getPatron(String value)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlDataAdapter sqlAdapt = null;

        sqlCon.Open();
        sqlAdapt = new SqlDataAdapter("exec usp_PERSON_FindPersonPatron '" + value + "'", sqlCon);
        sqlCon.Close();

        DataSet dataRows = new DataSet();
        sqlAdapt.Fill(dataRows);

        Patron ptn = new Patron();
        ptn.PersonID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[0].ToString());

        return ptn;
    }
    
    public Library getLibrary(String ID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlDataAdapter adapter = null;

        con.Open();
        adapter = new SqlDataAdapter("exec LIBRARY_SELECT '"+ID +"'", con);
        con.Close();

        DataSet dataRows = new DataSet();
        adapter.Fill(dataRows);

        Library lib = new Library();
        lib.LibraryID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[0].ToString());
        lib.Name = dataRows.Tables[0].Rows[0].ItemArray[1].ToString().Trim();
        lib.Motto = dataRows.Tables[0].Rows[0].ItemArray[2].ToString().Trim();
        lib.HeadLibrarian = dataRows.Tables[0].Rows[0].ItemArray[3].ToString().Trim();
        lib.PhoneNumber = dataRows.Tables[0].Rows[0].ItemArray[4].ToString().Trim();
        lib.Address = dataRows.Tables[0].Rows[0].ItemArray[5].ToString().Trim();
        lib.City = dataRows.Tables[0].Rows[0].ItemArray[6].ToString().Trim();
        lib.State = dataRows.Tables[0].Rows[0].ItemArray[7].ToString().Trim();
        lib.Email = dataRows.Tables[0].Rows[0].ItemArray[8].ToString().Trim();
        lib.Hours = dataRows.Tables[0].Rows[0].ItemArray[9].ToString().Trim();

        lib.FinePerDay = Convert.ToSingle(dataRows.Tables[0].Rows[0].ItemArray[10].ToString());
        lib.MaxFineToCheckOut = Convert.ToSingle(dataRows.Tables[0].Rows[0].ItemArray[11].ToString());
        lib.ReserveFee = Convert.ToSingle(dataRows.Tables[0].Rows[0].ItemArray[12].ToString());

        lib.CheckoutLength = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[13].ToString());
        lib.MaxTimesRenewable = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[14].ToString());
        lib.RenewLength = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[15].ToString());

        lib.Theme = dataRows.Tables[0].Rows[0].ItemArray[16].ToString().Trim();

        return lib;
    }
    public void updateLibrary(Library lib)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("LIBRARY_UPDATE", sqlCon);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.Add("@LibraryID", lib.LibraryID);
        com.Parameters.Add("@Name", lib.Name);
        com.Parameters.Add("@Motto", lib.Motto);
        com.Parameters.Add("@HeadLibrarian", lib.HeadLibrarian);
        com.Parameters.Add("@PhoneNumber", lib.PhoneNumber);
        com.Parameters.Add("@Address", lib.Address);
        com.Parameters.Add("@City", lib.City);
        com.Parameters.Add("@State", lib.State);
        com.Parameters.Add("@Email", lib.Email);
        com.Parameters.Add("@Hours", lib.Hours);

        com.Parameters.Add("@FinePerDay", lib.FinePerDay);
        com.Parameters.Add("@MaxFineToCheckOut", lib.MaxFineToCheckOut);
        com.Parameters.Add("@ReserveFee", lib.ReserveFee);

        com.Parameters.Add("@CheckoutLength", lib.CheckoutLength);
        com.Parameters.Add("@MaxTimesRenewable", lib.MaxTimesRenewable);
        //Response.Write("value = "+lib.MaxTimesRenewable);
        com.Parameters.Add("@RenewLength", lib.RenewLength);
        com.Parameters.Add("@Theme", lib.Theme);

        sqlCon.Open();
        com.ExecuteNonQuery();
        sqlCon.Close();
    }
    
    //Jacquie - get person searching by name, or Phone
    public DataSet findPerson_LibrarianView(String searchString, int libraryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_PATRON_GetPatronByIdNamePhone", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@value", searchString);
        com.Parameters.AddWithValue("@LibraryId", libraryId);

        con.Open();
        SqlDataAdapter sqlCommand = new SqlDataAdapter(com);
        con.Close();

        DataSet ds = new DataSet();
        sqlCommand.Fill(ds);

        return ds;
    }

    //Jacquie - get person searching by ID
    public DataSet findPersonById_LibrarianView(int PersonId, int libraryId)
    {
        SqlConnection con = new SqlConnection(connectionString);

        con.Open();
        SqlDataAdapter sqlCommand = new SqlDataAdapter("exec usp_PATRON_GetPatronById '" + PersonId + "', " + "'" + libraryId + "'", con);
        con.Close();

        DataSet ds = new DataSet();
        sqlCommand.Fill(ds);

        return ds;
    }

    //Jacquie - get the fines for a person
    public float getFines_LibrarianView(int personID)
    {
        float fine = -11;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_PATRON_GetFinesByPatronId", con);
        com.CommandType = CommandType.StoredProcedure;

        SqlParameter value = com.Parameters.Add("@PersonID", personID);
        SqlParameter returnValue = com.Parameters.Add("@ReturnValue", SqlDbType.Float);
        value.Direction = ParameterDirection.Input;
        returnValue.Direction = ParameterDirection.ReturnValue;

        value.Value = personID;

        con.Open();
        returnValue.Value = com.ExecuteScalar();
        con.Close();

        try
        {
            fine = float.Parse(returnValue.Value.ToString());
        }
        catch (Exception e)
        {
            string msg = e.Message.ToString();
        }

        return fine;
    }

    //Jacquie
    public void updateBook(Book b)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlCommand sqlCom = new SqlCommand("usp_BOOK_LibrarianBookUpdate", sqlCon);
        sqlCom.CommandType = CommandType.StoredProcedure;

        sqlCom.Parameters.Add("@Barcode", b.Barcode);
        sqlCom.Parameters.Add("@Author", b.Author);
        sqlCom.Parameters.Add("@CallNumber", b.CallNumber);
        sqlCom.Parameters.Add("@DatePublished", b.DatePublished);
        sqlCom.Parameters.Add("@ISBN", b.ISBN);
        sqlCom.Parameters.Add("@isYoungAdult", b.isYoungAdult);
        sqlCom.Parameters.Add("@Keywords", b.Keywords);
        sqlCom.Parameters.Add("@SeriesTitle", b.SeriesTitle);
        sqlCom.Parameters.Add("@Subject", b.Subject);
        sqlCom.Parameters.Add("@Title", b.Title);
        sqlCom.Parameters.Add("@LibraryID", b.LibraryID);
        //sqlCom.Parameters.Add("@PublisherID", b.PublisherID);

        sqlCon.Open();
        sqlCom.ExecuteNonQuery();
        sqlCon.Close();
    }

    //Jacquie
    public void insertBook(Book b)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlCommand sqlCom = new SqlCommand("usp_BOOK_LibrarianBookInsert", sqlCon);
        sqlCom.CommandType = CommandType.StoredProcedure;

        sqlCom.Parameters.Add("@Barcode", b.Barcode);
        sqlCom.Parameters.Add("@Author", b.Author);
        sqlCom.Parameters.Add("@CallNumber", b.CallNumber);
        sqlCom.Parameters.Add("@DatePublished", b.DatePublished);
        sqlCom.Parameters.Add("@ISBN", b.ISBN);
        sqlCom.Parameters.Add("@isYoungAdult", b.isYoungAdult);
        sqlCom.Parameters.Add("@Keywords", b.Keywords);
        sqlCom.Parameters.Add("@SeriesTitle", b.SeriesTitle);
        sqlCom.Parameters.Add("@Subject", b.Subject);
        sqlCom.Parameters.Add("@Title", b.Title);
        sqlCom.Parameters.Add("@LibraryID", b.LibraryID);
        //sqlCom.Parameters.Add("@PublisherID", b.PublisherID);

        sqlCon.Open();
        sqlCom.ExecuteNonQuery();
        sqlCon.Close();
    }

    //Checks to see if card number and password exist in database - Elizabeth
    public int getPatronLogin(int cardNum, string password)
    {
        int val;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand sqlCommand = new SqlCommand("PATRON_LOGIN", con);
        sqlCommand.CommandType = CommandType.StoredProcedure;

        SqlParameter param1 = sqlCommand.Parameters.Add("@cardNumber", SqlDbType.Int);
        SqlParameter param2 = sqlCommand.Parameters.Add("@password", SqlDbType.NChar);
        SqlParameter returnParam = sqlCommand.Parameters.Add("@count1", SqlDbType.Int);

        param1.Direction = ParameterDirection.Input;
        param2.Direction = ParameterDirection.Input;
        returnParam.Direction = ParameterDirection.ReturnValue;

        param1.Value = cardNum;
        param2.Value = password;

        con.Open();
        returnParam.Value = sqlCommand.ExecuteScalar();
        if ((int)returnParam.Value > 0)
            val = 1;
        else
            val = (int)returnParam.Value;
        con.Close();

        return val;
    }

    //get patron information - Elizabeth
    public Patron getPatronInfo(int personID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter sqlCommand = new SqlDataAdapter("exec PERSON_GetPerson '" + personID + "'", con);
        con.Close();

        DataSet dataRows = new DataSet();
        sqlCommand.Fill(dataRows);

        Patron patron = new Patron();
        patron.PersonID = personID;
        patron.Password = dataRows.Tables[0].Rows[0].ItemArray[1].ToString();
        //patron.isAdult = (bool)dataRows.Tables[0].Rows[0].ItemArray[2];
       // patron.wantsNotifications = (bool)dataRows.Tables[0].Rows[0].ItemArray[3];
        patron.PreferredContactMethod = dataRows.Tables[0].Rows[0].ItemArray[4].ToString();
        //patron.Fines = (float)(dataRows.Tables[0].Rows[0].ItemArray[5]);
        //patron.CardExpires = Convert.ToDateTime(dataRows.Tables[0].Rows[0].ItemArray[6]);

        return patron;
    }

    //get person information - Elizabeth
    public Person getPersonInfo(int personID)
    {
        SqlConnection con = new SqlConnection(connectionString);

        con.Open();
        SqlDataAdapter sqlCommand = new SqlDataAdapter("exec PERSON_LOGIN '" + personID + "'", con);
        con.Close();

        DataSet dataRows = new DataSet();
        sqlCommand.Fill(dataRows);

        Person person = new Person();
        person.PersonID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[0].ToString());
        person.FirstName = dataRows.Tables[0].Rows[0].ItemArray[1].ToString();
        person.LastName = dataRows.Tables[0].Rows[0].ItemArray[2].ToString();
        person.Address = dataRows.Tables[0].Rows[0].ItemArray[3].ToString();
        person.City = dataRows.Tables[0].Rows[0].ItemArray[4].ToString();
        person.State = dataRows.Tables[0].Rows[0].ItemArray[5].ToString();
        person.Zip = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[6].ToString());
        person.PhoneNumber = dataRows.Tables[0].Rows[0].ItemArray[7].ToString();
        person.Email = dataRows.Tables[0].Rows[0].ItemArray[8].ToString();
        //person.wantsNewsletter = Convert.ToBoolean(dataRows.Tables[0].Rows[0].ItemArray[8].ToString());

        return person;
    }

    //get all people who belong to a particular library
    public DataSet getAllPeopleAtLibrary(int libraryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter sqlCommand = new SqlDataAdapter("exec usp_PERSON_GetAllPeopleAtLibrary '" + libraryId + "'", con);
        con.Close();

        DataSet ds = new DataSet();
        sqlCommand.Fill(ds);

        return ds;
    }

    //Get all the people who belong to the particular library who want the newsletter
    public DataSet getPeopleWhoWantNewsletter(int LibraryID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec Person_Select_WantsNewsletter " + LibraryID, con);
        con.Close();
        DataSet ds = new DataSet();
        command.Fill(ds);
        return ds;
    }

    //get books checked out by patron - Elizabeth
    public SqlDataAdapter getBooksCheckedOut(int personID)
    {
        SqlConnection con = new SqlConnection(connectionString);

        con.Open();
        SqlDataAdapter sqlCommand = new SqlDataAdapter("exec PATRON_BOOKS_CHECKED_OUT '" + personID + "'", con);
        con.Close();

        return sqlCommand;
    }

    public SqlDataAdapter getStates()
    {
        SqlConnection con = new SqlConnection(connectionString);

        SqlDataAdapter results = null;

        con.Open();

        results = new SqlDataAdapter("exec sp_GetStates", con);

        con.Close();

        return results;
    }

    //checks to see if person is an employee - Elizabeth
    public int isEmployee(int personID)
    {
        int val;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand sqlCommand = new SqlCommand("EMPLOYEE_IsEmployee", con);
        sqlCommand.CommandType = CommandType.StoredProcedure;

        SqlParameter param1 = sqlCommand.Parameters.Add("@value", SqlDbType.Int);
        SqlParameter returnParam = sqlCommand.Parameters.Add("@count1", SqlDbType.Int);

        param1.Direction = ParameterDirection.Input;
        returnParam.Direction = ParameterDirection.ReturnValue;

        param1.Value = personID;

        con.Open();
        returnParam.Value = sqlCommand.ExecuteScalar();
        if ((int)returnParam.Value > 0)
            val = 1;
        else
            val = (int)returnParam.Value;
        con.Close();

        return val;
    }

    //checks to see if book is renewable - Elizabeth
    public int isRenewable(string barcode)
    {
        int val;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand sqlCommand = new SqlCommand("RESERVEDBOOK_IsRenewable", con);
        sqlCommand.CommandType = CommandType.StoredProcedure;

        SqlParameter param1 = sqlCommand.Parameters.Add("@value", SqlDbType.NVarChar);
        SqlParameter returnParam = sqlCommand.Parameters.Add("@count1", SqlDbType.Int);

        param1.Direction = ParameterDirection.Input;
        returnParam.Direction = ParameterDirection.ReturnValue;

        param1.Value = barcode;

        con.Open();
        returnParam.Value = sqlCommand.ExecuteScalar();
        if ((int)returnParam.Value > 0)
            val = 1;
        else
            val = (int)returnParam.Value;
        con.Close();

        return val;
    }

    //get all of a patron's information - Elizabeth
    public SqlDataAdapter getPatronAcct(int personID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("PATRON_PERSON_SELECT " + personID, con);
        con.Close();

        return command;
    }
    public DataSet getAllAnnouncements(int LibraryID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec ANNOUNCEMENT_SELECT " + LibraryID, con);
        con.Close();
        DataSet ds = new DataSet();
        command.Fill(ds);
        return ds;
    }
    public DataSet compileAnnouncements(int LibraryID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec ANNOUNCEMENT_CompileList " + LibraryID, con);
        con.Close();
        DataSet ds = new DataSet();
        command.Fill(ds);
        return ds;
    }
    public DataSet getAllEvents(int LibraryID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec EVENT_SELECT " + LibraryID, con);
        con.Close();
        DataSet ds = new DataSet();
        command.Fill(ds);
        return ds;
    }
    public DataSet compileEvents(int LibraryID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec EVENT_CompileList " + LibraryID, con);
        con.Close();
        DataSet ds = new DataSet();
        command.Fill(ds);
        return ds;
    }

    public Event getEvent(int EventID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec EVENT_SELECT_WHERE_ID " + EventID, con);
        con.Close();
        DataSet ds = new DataSet();
        command.Fill(ds);

        Event ev = new Event();
        ev.EventID = Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray[0].ToString());
        ev.Title = ds.Tables[0].Rows[0].ItemArray[1].ToString();
        ev.Description = ds.Tables[0].Rows[0].ItemArray[2].ToString();
        ev.dateTime = Convert.ToDateTime(ds.Tables[0].Rows[0].ItemArray[3]);
        ev.Location = ds.Tables[0].Rows[0].ItemArray[4].ToString();
        ev.ContactID = Convert.ToInt32(ds.Tables[0].Rows[0].ItemArray[5].ToString());

        return ev;
    }
    public DataSet findEvents(int LibraryID, String criteria)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec EVENT_SELECT_LIKE '" + LibraryID+"','"+criteria+"'", con);
        con.Close();
        DataSet ds = new DataSet();
        command.Fill(ds);
        return ds;
    }

    //get the details for a particular book - Elizabeth
    public Book getBookDetails(string barcode)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec BOOK_GetDetails " + barcode, con);
        con.Close();

        DataSet dataRows = new DataSet();
        command.Fill(dataRows);

        Book book = new Book();
        book.Barcode = dataRows.Tables[0].Rows[0].ItemArray[0].ToString().Trim();
        book.ISBN = Convert.ToInt64(dataRows.Tables[0].Rows[0].ItemArray[1].ToString());
        book.isYoungAdult =Convert.ToBoolean(dataRows.Tables[0].Rows[0].ItemArray[2]);
        book.Title = dataRows.Tables[0].Rows[0].ItemArray[3].ToString().Trim();
        book.SeriesTitle = dataRows.Tables[0].Rows[0].ItemArray[4].ToString().Trim();
        book.Author = dataRows.Tables[0].Rows[0].ItemArray[5].ToString().Trim();
        book.PublisherID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[6].ToString());
        book.DatePublished = Convert.ToDateTime(dataRows.Tables[0].Rows[0].ItemArray[7].ToString());
        book.Subject = dataRows.Tables[0].Rows[0].ItemArray[8].ToString().Trim();
        book.Keywords = dataRows.Tables[0].Rows[0].ItemArray[9].ToString().Trim();
        book.CallNumber = dataRows.Tables[0].Rows[0].ItemArray[10].ToString().Trim();
        book.LibraryID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[11].ToString());

        return book;
    }

    public Publisher getPublisher(int id)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlDataAdapter adapter = null;

        con.Open();
        adapter = new SqlDataAdapter("exec PUBLISHER_GetDetails '" + id + "'", con);
        con.Close();

        DataSet dataRows = new DataSet();
        adapter.Fill(dataRows);

        Publisher pub = new Publisher();
        pub.PublisherID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[0].ToString());
        pub.Name = dataRows.Tables[0].Rows[0].ItemArray[1].ToString().Trim();
        pub.City = dataRows.Tables[0].Rows[0].ItemArray[2].ToString().Trim();
        pub.State = dataRows.Tables[0].Rows[0].ItemArray[3].ToString().Trim();
        pub.Country = dataRows.Tables[0].Rows[0].ItemArray[4].ToString().Trim();

        return pub;
    }

    //checks to see if a book is checked out - Elizabeth
    public int isBookCheckedOut(string barcode)
    {
        int val;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand sqlCommand = new SqlCommand("CHECKEDOUTBOOK_IsBookCheckedOut", con);
        sqlCommand.CommandType = CommandType.StoredProcedure;

        SqlParameter param1 = sqlCommand.Parameters.Add("@barcode", SqlDbType.NVarChar);
        SqlParameter returnParam = sqlCommand.Parameters.Add("@count1", SqlDbType.Int);

        param1.Direction = ParameterDirection.Input;
        returnParam.Direction = ParameterDirection.ReturnValue;

        param1.Value = barcode;

        con.Open();
        returnParam.Value = sqlCommand.ExecuteScalar();
        if ((int)returnParam.Value > 0)
            val = 1;
        else
            val = (int)returnParam.Value;
        con.Close();

        return val;
    }

    //checks to see if a book exists
    //Jacquie
    public int bookExists(string barcode)
    {
        int rowsAffected;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_BOOK_Exist", con);
        com.CommandType = CommandType.StoredProcedure;

        SqlParameter value = com.Parameters.Add("@Value", SqlDbType.NChar);
        SqlParameter returnValue = com.Parameters.Add("@ReturnValue", SqlDbType.NChar);
        value.Direction = ParameterDirection.Input;
        returnValue.Direction = ParameterDirection.ReturnValue;

        value.Value = barcode;

        con.Open();
        returnValue.Value = com.ExecuteScalar();
        if ((int)returnValue.Value > 0)
            rowsAffected = 1;
        else
            rowsAffected = (int)returnValue.Value;
        con.Close();

        return rowsAffected;
    }

    //checks to see if a book is checked in
    //Jacquie
    public int checkedOutBookExists(string barcode)
    {
        int rowsAffected;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_BOOK_CheckedOutBook_Exist", con);
        com.CommandType = CommandType.StoredProcedure;

        SqlParameter value = com.Parameters.Add("@Value", SqlDbType.NChar);
        SqlParameter returnValue = com.Parameters.Add("@ReturnValue", SqlDbType.NChar);
        value.Direction = ParameterDirection.Input;
        returnValue.Direction = ParameterDirection.ReturnValue;

        value.Value = barcode;

        con.Open();
        returnValue.Value = com.ExecuteScalar();
        if ((int)returnValue.Value > 0)
            rowsAffected = 1;
        else
            rowsAffected = (int)returnValue.Value;
        con.Close();

        return rowsAffected;
    }

    //checks to see if a book is checked in
    //Jacquie
    public int checkedOutBookReturnedExists(string barcode)
    {
        int rowsAffected;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_BOOK_CheckedOutBookReturned_Exist", con);
        com.CommandType = CommandType.StoredProcedure;

        SqlParameter value = com.Parameters.Add("@Value", SqlDbType.NChar);
        SqlParameter returnValue = com.Parameters.Add("@ReturnValue", SqlDbType.NChar);
        value.Direction = ParameterDirection.Input;
        returnValue.Direction = ParameterDirection.ReturnValue;

        value.Value = barcode;

        con.Open();
        returnValue.Value = com.ExecuteScalar();
        if ((int)returnValue.Value > 0)
            rowsAffected = 1;
        else
            rowsAffected = (int)returnValue.Value;
        con.Close();

        return rowsAffected;
    }

    //gets the details of a checked out book - Elizabeth
    public CheckedOutBook checkedOutBookDetails(string barcode)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlDataAdapter adapter = null;

        con.Open();
        adapter = new SqlDataAdapter("exec CHECKEDOUTBOOK_BookCheckedOutDetails '" + barcode + "'", con);
        con.Close();

        DataSet dataRows = new DataSet();
        adapter.Fill(dataRows);

        CheckedOutBook book = new CheckedOutBook();
        book.CheckOutID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[0].ToString());
        book.Barcode = dataRows.Tables[0].Rows[0].ItemArray[1].ToString().Trim();
        book.PersonID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[2].ToString().Trim());
        book.dateCheckedOut = Convert.ToDateTime(dataRows.Tables[0].Rows[0].ItemArray[3].ToString().Trim());
        book.dueDate = Convert.ToDateTime(dataRows.Tables[0].Rows[0].ItemArray[4].ToString().Trim());
        book.status = dataRows.Tables[0].Rows[0].ItemArray[5].ToString().Trim();

        return book;
    }

    //updates a checked out book's status when a patron renews the book - Elizabeth
    public void updateCheckedOutBook(CheckedOutBook b)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand("CHECKEDOUTBOOK_RenewBook", con);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@Barcode", b.Barcode);
        command.Parameters.Add("@CheckOutID", b.CheckOutID);
        command.Parameters.Add("@PersonID", b.PersonID);
        command.Parameters.Add("@DateCheckedOut", b.dateCheckedOut);
        command.Parameters.Add("@newDueDate", b.dueDate);
        command.Parameters.Add("@status", b.status);

        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }

    //gets the current date according to sql server - Elizabeth
    public DateTime getDate()
    {
        SqlConnection con = new SqlConnection(connectionString);
        DateTime date = new DateTime();

        con.Open();
        SqlDataAdapter adapter = new SqlDataAdapter("GetDate", con);
        con.Close();

        DataSet dataRows = new DataSet();
        adapter.Fill(dataRows);

        date = Convert.ToDateTime(dataRows.Tables[0].Rows[0].ItemArray[0].ToString().Trim());

        return date;
    }
    
    //checks out a book for checkOutBook.ascx
    //jacquie 
    public void checkOutBook(CheckedOutBook b)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_BOOK_CheckOutBooks_Insert", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@Barcode", b.Barcode);
        com.Parameters.AddWithValue("@PersonID", b.PersonID);
        com.Parameters.AddWithValue("@DateCheckedOut", b.dateCheckedOut);
        com.Parameters.AddWithValue("@DueDate", b.dueDate);
        com.Parameters.AddWithValue("@Status", b.status);

        con.Open();
        com.ExecuteNonQuery();
        con.Close();
    }

    //checks in a book for checkInBook.ascx
    //jacquie
    public int checkInBook(String barcode)
    {
        int rowsAffected;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_BOOK_CheckInBooks", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@Barcode", barcode);

        con.Open();
        rowsAffected = com.ExecuteNonQuery();
        con.Close();

        return rowsAffected;
    }

    //updates status of reserved book for Librarian View
    //Jacquie
    public int UpdateReservedBookStatus_LibrarianView(int reserveId, string status)
    {
        int rowsAffected;
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_RESERVEDBOOK_UpdateStatus", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@ReserveId", reserveId);
        com.Parameters.AddWithValue("@Status", status);

        con.Open();
        rowsAffected = com.ExecuteNonQuery();
        con.Close();

        return rowsAffected;
    }

    //for Librarian view reserved books
    //Jacquie
    public SqlDataAdapter GetReservedBooks_LibrarianView(int libraryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter sqlDA = new SqlDataAdapter("exec usp_RESERVEDBOOK_BooksToPull'" + libraryId + "'", con);
        con.Close();
        return sqlDA;
    }

    //for Librarian view reserved books
    //returns list of books ready for pickup
    //Jacquie
    public SqlDataAdapter GetReadyBooks_LibrarianView(int libraryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter sqlDA = new SqlDataAdapter("exec usp_RESERVEDBOOK_ReadyForPickup'" + libraryId + "'", con);
        con.Close();
        return sqlDA;
    }

    //for librarian view events
    //gets events matching search terms
    //Jacquie
    public SqlDataAdapter GetEvents_LibrarianView(string s, int libraryId)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter sqlDA = new SqlDataAdapter("exec usp_EVENT_LibrarianEventsGrid'" + s + "','" + libraryId + "'", con);
        con.Close();
        return sqlDA;
    }

    //for librarian view events
    //updates events matching search terms
    //Jacquie
    public int UpdateEvents_LibrarianView(int EventId, String Title, String Description, DateTime dateTime, String Location)
    {
        int affectedRows;

        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_EVENT__LibrarianUpdate", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@EventId", EventId);
        com.Parameters.AddWithValue("@Title",Title);
        com.Parameters.AddWithValue("@Description",Description);
        com.Parameters.AddWithValue("@dateTime",dateTime);
        com.Parameters.AddWithValue("@Location", Location);

        con.Open();
        affectedRows = com.ExecuteNonQuery();
        con.Close();

        return affectedRows;
    }

    //for librarian view events
    //enters an event
    //Jacquie
    public int EnterEvent_LibrarianView(Event anEvent)
    {
        int affectedRows;

        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand com = new SqlCommand("usp_EVENT_LibrarianInsert", con);
        com.CommandType = CommandType.StoredProcedure;

        com.Parameters.AddWithValue("@Title", anEvent.Title);
        com.Parameters.AddWithValue("@Description", anEvent.Description);
        com.Parameters.AddWithValue("@dateTime", anEvent.dateTime);
        com.Parameters.AddWithValue("@Location", anEvent.Location);
        com.Parameters.AddWithValue("@ContactID", anEvent.ContactID);
        com.Parameters.AddWithValue("@LibraryID", anEvent.LibraryID);

        con.Open();
        affectedRows = com.ExecuteNonQuery();
        con.Close();

        return affectedRows;
    }

    //for librarian view announcements
    //updates announcements
    //Jacquie
    public int UpdateAnnouncements_LibrarianView(Announcement a)
    {
        SqlConnection sqlCon = new SqlConnection(connectionString);
        SqlCommand sqlCom = new SqlCommand("usp_ANNOUNCEMENT_LibrarianUpdate", sqlCon);
        sqlCom.CommandType = CommandType.StoredProcedure;

        sqlCom.Parameters.Add("@Description", a.Description);
        sqlCom.Parameters.Add("@Title", a.Title);
        sqlCom.Parameters.Add("@AnnouncementID", a.AnnouncementID);

        sqlCon.Open();
        int affectedRows = sqlCom.ExecuteNonQuery();
        sqlCon.Close();

        return affectedRows;
    }

    //for checkOutBook.ascx
    //returns list of books checked out today by patron
    //Jacquie
    public SqlDataAdapter GetBooksCheckedOut_LibrarianView(string personID)
    {
        SqlConnection con = new SqlConnection(connectionString);

        con.Open();
        SqlDataAdapter sqlDA = new SqlDataAdapter("exec usp_BOOK_CheckOutBooks_Grid '" + personID + "'", con);
        con.Close();

        return sqlDA;
    }

    //gets books reserved by a patron - Elizabeth
    public SqlDataAdapter getPatronReservedBooks(int personID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter adapter = new SqlDataAdapter("exec RESERVEDBOOK_Select '" + personID + "'", con);
        con.Close();

        return adapter;
    }

    //gets the details of a reserved book - Elizabeth
    public ReservedBook getReservedBook(int reserveID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter adapter = new SqlDataAdapter("exec RESERVEDBOOK_GetDetails '" + reserveID + "'", con);
        con.Close();

        DataSet dataRows = new DataSet();
        adapter.Fill(dataRows);

        ReservedBook book = new ReservedBook();
        book.ReserveID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[0].ToString());
        book.Barcode = dataRows.Tables[0].Rows[0].ItemArray[1].ToString().Trim();
        book.PersonID = Convert.ToInt32(dataRows.Tables[0].Rows[0].ItemArray[2].ToString().Trim());
        book.DateReserved = Convert.ToDateTime(dataRows.Tables[0].Rows[0].ItemArray[3].ToString().Trim());
        book.ReserveStatus = dataRows.Tables[0].Rows[0].ItemArray[4].ToString().Trim();

        return book;
    }

    //updates the status of a reserved boo when it is canceled - Elizabeth
    public void updateReservedBook(ReservedBook book)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand("RESERVEDBOOK_Cancel", con);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.AddWithValue("@reserveID", book.ReserveID);
        command.Parameters.AddWithValue("@barcode", book.Barcode);
        command.Parameters.AddWithValue("@personID", book.PersonID);
        command.Parameters.AddWithValue("@date", book.DateReserved);
        command.Parameters.AddWithValue("@status", book.ReserveStatus);

        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }

    //gets un-notified reserved books that are ready to pick up
    //Kayla
    public DataSet getReservedBooksToNotify()
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("exec RESERVEDBOOK_Select_ToNotify", con);
        con.Close();
        DataSet ds = new DataSet();
        command.Fill(ds);
        return ds;
    }

    //inserts a new book reservation - Elizabeth
    public void addReservedBook(ReservedBook book)
    {
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand("RESERVEDBOOK_Insert", con);
        command.CommandType = CommandType.StoredProcedure;

        command.Parameters.Add("@barcode", book.Barcode);
        command.Parameters.Add("@personID", book.PersonID);
        command.Parameters.Add("@dateReserved", book.DateReserved);
        command.Parameters.Add("@reserveStatus", book.ReserveStatus);

        con.Open();
        command.ExecuteNonQuery();
        con.Close();
    }

    //gets the closest due date of a patron's checked out books
    public DateTime getDueDate(int personID)
    {
        SqlConnection con = new SqlConnection(connectionString);
        con.Open();
        SqlDataAdapter command = new SqlDataAdapter("PATRON_CHECKEDOUTBOOKS_DueDate '" + personID + "'", con);
        con.Close();

        DataSet dataRows = new DataSet();
        command.Fill(dataRows);

        DateTime date = new DateTime();
        date = Convert.ToDateTime(dataRows.Tables[0].Rows[0].ItemArray[0].ToString().Trim());
        return date;


    }
}
